# encoding: utf-8

from openpyxl import load_workbook

wb = load_workbook(r'E:\Personal Files\PwC\Salesforce\AdvancedAdmin认证\SFDC Advanced Admin题库整理-完整解析.xlsx')


def write_option(sheet):
    # 遍历表格
    for row_num in range(2, 200):
        question = sheet.cell(row=row_num, column=1).value
        if question is None:
            continue
        answer = sheet.cell(row=row_num, column=8).value.strip()
        options = sheet.cell(row=row_num, column=2).value
        # 分割选项
        option_list = options.split("\n")
        # print(option_list)
        option_map = {}
        option_number_list = ['A','B','C','D','E']
        for i in range(len(option_list)):
            if option_list[i] in option_number_list:
                option_map[option_list[i]] = ''
            else:
                option_map[option_list[i - 1]] = option_list[i]
        print(f"选项数量：{len(option_map)} 选项内容：{option_map}")
        # 写入选项
        sheet.cell(row_num, 3, option_map.get('A',''))
        sheet.cell(row_num, 4, option_map.get('B',''))
        sheet.cell(row_num, 5, option_map.get('C',''))
        sheet.cell(row_num, 6, option_map.get('D',''))
        sheet.cell(row_num, 7, option_map.get('E',''))
        # 写入类型
        if(len(answer) > 1):
            sheet.cell(row_num, 2, "多选题")
        else:
            sheet.cell(row_num, 2, "单选题")
    wb.save(r'E:\Personal Files\PwC\Salesforce\AdvancedAdmin认证\SFDC Advanced Admin题库整理-完整解析.xlsx')

def write_type(sheet):
    for row_num in range(2, 200):
        answer = sheet.cell(row=row_num, column=8).value
        if answer is None:
            continue
        else:
            answer = answer.strip()
        # 写入类型
        if(len(answer) > 1):
            sheet.cell(row_num, 2, "多选题")
        else:
            sheet.cell(row_num, 2, "单选题")
    wb.save(r'E:\Personal Files\PwC\Salesforce\AdvancedAdmin认证\SFDC Advanced Admin题库整理-完整解析.xlsx')

if __name__ == '__main__':
    for number in range(9):
        sheet = wb.worksheets[number]
        # write_option(sheet)
        write_type(sheet)